package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.condition.MemberCondition;
import com.fenrirtec.aepp.common.dao.TenderEnrollDao;
import com.fenrirtec.aepp.common.dto.TenderQuoteDto;
import com.fenrirtec.aepp.common.model.TenderEnrollList;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class TenderEnrollDao_JDBCImpl implements TenderEnrollDao{
	private static final Logger logger = LoggerFactory.getLogger(TenderEnrollDao_JDBCImpl.class);
    @Override
    public List<TenderEnrollList> searchByCnd(MemberCondition condition ) {
        if (logger.isInfoEnabled()) {
            logger.info("[TenderEnrollDao_JDBCImpl#searchByCnd] start.");
        }

        PreparedStatement statement = null;
        
        try {
            
            StringBuilder sql = new StringBuilder()
            .append("select \n")
            .append("  tte.tender_id, \n")
            .append("tte.member_login_name, \n")
            .append("tte.enroll_date ,\n")
            .append("tte.description ,\n")
            .append("tte.finalist_flag ,\n")
            .append("tte.invite_flag ,\n")
            .append("vti.tender_title ,\n")
            .append("vti.enroll_start_date ,\n")
            .append("vti.enroll_end_date ,\n")
            .append("vti.tender_end_date ,\n")
            .append("vti.publish_result_date ,\n")
            .append("vti.content_description ,\n")
            .append("vti.vendor_requirement ,\n")
            .append("vti.tender_pattern ,\n")
            .append("vti.tender_template ,\n")
            .append("vti.contact_name ,\n")
            .append("vti.contact_tel ,\n")
            .append("vti.tender_pattern_name ,\n")
            .append("vti.status_name ,\n")
            .append("vti.status ,\n")
            .append("vti.enroll_count ,\n")
            .append("vti.finalist_count ,\n")
            .append("vti.quote_count \n ,")
            .append("vti.tender_no \n")
            .append("from \n")
            .append("  t_tender_enroll tte  \n")
            .append("join \n")
            .append("v_tender_information vti  \n")
            .append("on  \n")
            .append("(vti.tender_id =tte.tender_id) \n") 
            .append("where \n")
            .append("  vti.delete_flag = false \n");
            if (condition.getTenderId() != null) {
                sql.append("and \n");
                sql.append("  tte.tender_id  = ? \n");
            }
            if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
                sql.append("and \n");
                sql.append("  tte.member_login_name= ? \n");
            }
            sql.append("order by \n");
            sql.append("  vti.tender_id desc \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderEnrollDao_JDBCImpl#searchByCnd] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            if (condition != null) {
                int index = 0;
                if (condition.getTenderId() != null){
                    statement.setInt(++index, condition.getTenderId());
                }
                if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
                    statement.setString(++index, condition.getMemberLoginName());
                }
            }

            ResultSet resultSet = statement.executeQuery();
            ResultSetMapper<TenderEnrollList> resultSetMapper = new ResultSetMapper<TenderEnrollList>();
            List<TenderEnrollList> tenderDetailList = resultSetMapper.mapResultSetToList(resultSet, TenderEnrollList.class);
            
            return tenderDetailList;
            
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderEnrollDao_JDBCImpl#searchByCnd] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[TenderEnrollDao_JDBCImpl#searchByCnd] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }
	@Override
	public void updateTenderEnroll(String memberLoginName) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderEnrollDao_JDBCImpl#updateTenderEnroll] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("update \n")
			.append(" t_tender_enroll \n")
			.append(" set \n")
			.append(" delete_flag = ? \n")
			.append(" where \n")
			.append(" member_login_name = ?\n");
			
			if(logger.isTraceEnabled()){
				logger.trace("[TenderEnrollDao_JDBCImpl#updateTenderEnroll] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setBoolean(1, true);
			statement.setString(2, memberLoginName);
			statement.executeUpdate();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderEnrollDao_JDBCImpl#updateTenderEnroll] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderEnrollDao_JDBCImpl#updateTenderEnroll] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}

		
	}
	@Override
	public void insertTenderQuote(TenderQuoteDto tenderQuoteDto) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderEnrollDao_JDBCImpl#insertTenderQuote] start.");
		}
		PreparedStatement statement=null;
		try{
			
			StringBuffer sql=new StringBuffer()
			.append("insert into t_tender_quote( \n")
			.append(" tender_id,\n")
			.append(" member_login_name,\n")
			.append(" quote_date,\n")
			.append(" total_price,\n")
			.append(" create_user,\n")
			.append(" create_date,\n")
			.append(" update_user,\n")
			.append(" update_date)\n")
			.append("values( \n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? ,\n")
			.append(" ? )\n");
			if(logger.isTraceEnabled()){
				logger.trace("[TenderEnrollDao_JDBCImpl#insertTenderQuote] sql={}",sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());
			
			statement.setInt(1, tenderQuoteDto.getTenderId());
			statement.setString(2, tenderQuoteDto.getMemberLoginName());
			String quoteDatestr = tenderQuoteDto.getQuoteDate();
			String createDatestr = tenderQuoteDto.getCreateDate();
			String updateDatestr = tenderQuoteDto.getUpdateDate();
			SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");	
			Date quoteDate=null;
			Date createDate=null;
			Date updateDate=null;
			try {
				quoteDate=sim.parse(quoteDatestr);
				createDate=sim.parse(createDatestr);
				updateDate=sim.parse(updateDatestr);
			} catch (ParseException e) {
				
				e.printStackTrace();
			}
			
			
			statement.setDate(3,  new java.sql.Date(quoteDate.getTime()));
			statement.setDouble(4, tenderQuoteDto.getTotalPrice());
			statement.setString(5, "system");
			statement.setDate(6, new java.sql.Date(createDate.getTime()));
			statement.setString(7, "system");
			statement.setDate(8, new java.sql.Date(updateDate.getTime()));
			statement.execute();
			
		}catch(SQLException e){
			if(logger.isTraceEnabled()){
				logger.trace("[TenderEnrollDao_JDBCImpl#insertTenderQuote] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderEnrollDao_JDBCImpl#insertTenderQuote] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}

		
	}
}
